package com.mtoolkit.jdbc.support;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mtoolkit.jdbc.PersistenceException;
import com.mtoolkit.page.Page;

/**
 * Jdbc template implementation for MYSQL database.
 * 
 * @author 	<a href="mailto:xishizhang@gmail.com">ZhangShixi</a>
 * @version 1.0, 12/8/2011
 * @since 	JDK1.5
 */
public class MysqlJdbcTemplate extends AbstractJdbcTemplate {
	
	/**
	 * Default constructor.
	 */
	public MysqlJdbcTemplate() {
	}
	
	/**
     * {@inheritDoc}	
     *
     * @param  conn   	 {@inheritDoc}
     * @param  keySql	 {@inheritDoc}
     * @param  insertSql {@inheritDoc}
     * @param  params 	 {@inheritDoc}
     * @param  batchSize {@inheritDoc}
     * 
     * @return {@inheritDoc}
     * 
     * @throws NullPointerException 	{@inheritDoc}
     * @throws IllegalArgumentException {@inheritDoc}
     * @throws PersistenceException		{@inheritDoc}	
     */
	@Override
	public Number[] executeKeyInsert(Connection conn, 
									 String keySql,
									 String insertSql,
									 Object[][] params, 
									 int batchSize) {
		ResultSet keySet = null;
		Number[] returnKeys = null;
		PreparedStatement stmt = null;

		checkConnection(conn);
		checkSql(insertSql);
		
		try {
			checkGeneratedKeysSupport(conn);
			
			stmt = prepareStatement(conn, insertSql, Statement.RETURN_GENERATED_KEYS);
			
			if (params == null) {
				stmt.executeUpdate();
				keySet = stmt.getGeneratedKeys();
				while (keySet.next()) {
					returnKeys = new Number[]{(Number) keySet.getObject(1)};
					break;
				}
				
			} else {
				returnKeys = new Number[params.length]; 
				int newBatchSize = batchSize < params.length ? batchSize : params.length;
				int index = 0;
				int remaining = 0;
				
				for (int i = 0; i < params.length; i++) {
					fillStatement(stmt, params[i]);
					stmt.addBatch();
					
					remaining = (i + 1) % newBatchSize; 
					if (remaining == 0) {
						stmt.executeBatch();
						
						keySet = stmt.getGeneratedKeys();
						while (keySet.next()) {
							returnKeys[index++] = (Number) keySet.getObject(1);
						}
						
						close(keySet);
					}
				}
				
				// execute remaining statements.
				if (remaining != 0) {
					stmt.executeBatch();
					
					keySet = stmt.getGeneratedKeys();
					while (keySet.next()) {
						returnKeys[index++] = (Number) keySet.getObject(1);
					}
					
					close(keySet);
				}
			}
			
			return returnKeys;
		} catch (SQLException e) {
			throw new PersistenceException(e.getMessage(), e);
		} finally {
			try {
				close(stmt);
			} catch(SQLException e) {
				throw new PersistenceException(e.getMessage(), e);
			}
		}
	}
	
	/**
	 * Builds actually execute SQL statement with specified query page 
	 * info for MYSQL database. Examples:
	 * <pre>
	 * 		String sql = 
	 * 			"select TABLE_COLUMNS from FIRST_TABLE ft " +
	 * 			"left join SECOND_TABLE st on ft.ID = st.ID " +
	 *			"order by ft.TABLE_COLUMN desc";
	 *
	 *		Page page = new Page(2, 5);
	 * 		page.setTotalData(20);
	 * 		
	 * 		doFileExecuteSql(sql, page) = 
	 * 			"select TABLE_COLUMNS from FIRST_TABLE ft " +
	 * 			"left join SECOND_TABLE st on ft.ID = st.ID " +
	 *			"order by ft.TABLE_COLUMN desc limit 5, 5";
	 * </pre>
	 * 
	 * @param  sql  SQL statement template.
	 * @param  page query page info.
	 * 
	 * @return actually execute SQL statement witch fills query page info.
	 */
	@Override
	protected String doFillExecuteSql(String sql, Page<?> page) {
		int from = page.getFromIndex();
		int to   = page.getToIndex();
		
		StringBuilder sqlBuf = new StringBuilder(sql);
		sqlBuf.append(" limit ")
			  .append(from)
			  .append(",")
			  .append(to - from + 1);
		
		return sqlBuf.toString();
	}

}
